{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "596f8912-5fa6-4573-8f20-1d1d2d479a49",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from perspective.widget import PerspectiveWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "789879db-bb20-4809-af4d-906f7ed584da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花费</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pl.read_parquet(\"stock_trades.parquet\")\n",
    "df = df.sort(\"交易日期\", \"交易时间\", \"证券代码\")\n",
    "df = df.with_columns(\n",
    "    pl.col(\"交易时间\").cast(pl.String),\n",
    "    手续费率=pl.col(\"手续费\") / pl.col(\"成交金额\"),\n",
    "    印花税率=pl.col(\"印花费\") / pl.col(\"成交金额\"),\n",
    "    过户费率=pl.col(\"过户费\") / pl.col(\"成交金额\"),\n",
    ")\n",
    "df = df.with_row_index(\"序号\", 1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "17902427-ce94-4e92-bac5-1d7be2305869",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6c28bd2352b641f3992767a2a9d67026",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "da33b4a0-3a79-42e2-b04b-5e7d185930a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = df.join(\n",
    "    df.group_by(\"证券代码\", \"证券名称\")\n",
    "    .agg(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .sum()\n",
    "        ),\n",
    "    )\n",
    "    .filter(pl.col(\"结余数量\") < 0),\n",
    "    on=\"证券代码\",\n",
    "    how=\"anti\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "f3a4dcb2-2438-44e6-a115-4ff49c0e0a74",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花费</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2901f2ed-4566-47c1-ba33-68309db25e80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = df[\"交易日期\"].min()\n",
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a590ec06-111c-489a-b1f4-182cb2736b16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2023, 10, 31)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_date = df[\"交易日期\"].max()\n",
    "end_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4a91801d-c90b-489e-9d47-4296a6d7026c",
   "metadata": {},
   "outputs": [],
   "source": [
    "k1 = df.select(日期=pl.date_range(start_date, end_date))\n",
    "k2 = df[\"证券代码\"].unique().sort().to_frame()\n",
    "k = k1.join(k2, how=\"cross\")\n",
    "d2 = (\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    .sort([\"日期\", \"证券代码\"])\n",
    "    .with_columns(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .otherwise(0)\n",
    "            .cum_sum()\n",
    "            .over(\"证券代码\")\n",
    "        ),\n",
    "    )\n",
    "    .filter(pl.col.结余数量 > 0)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b67f47c6-ce02-4613-9ca2-585a41872dfd",
   "metadata": {},
   "source": [
    "PerspectiveWidget(d2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "b6236fd8-38b5-4dff-a9c1-a2c7f5f83b15",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "\n",
    "pro = ts.pro_api()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "bad5496a-b35b-432e-8473-0f0e32fe25c2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "8ec8caed-8e6a-4ea0-a367-da58e13efcec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20220711'"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"{start_date:%Y%m%d}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "8dd1f568-c24b-455c-8003-a60db58efc25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20231031'"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"{end_date:%Y%m%d}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97ec9464-7f4c-49ea-9713-41c71c7b4779",
   "metadata": {},
   "outputs": [],
   "source": [
    "str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "85414058-787c-4b9b-943f-eb56c6108e21",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231031&quot;</td><td>14.75</td><td>14.9</td><td>14.59</td><td>14.7</td><td>14.75</td><td>-0.05</td><td>-0.339</td><td>65859.96</td><td>96984.271</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231030&quot;</td><td>13.88</td><td>14.89</td><td>13.88</td><td>14.75</td><td>13.93</td><td>0.82</td><td>5.8866</td><td>123932.16</td><td>180119.372</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231027&quot;</td><td>13.7</td><td>13.98</td><td>13.51</td><td>13.93</td><td>13.64</td><td>0.29</td><td>2.1261</td><td>35782.0</td><td>49386.168</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231026&quot;</td><td>13.49</td><td>13.68</td><td>13.4</td><td>13.64</td><td>13.62</td><td>0.02</td><td>0.1468</td><td>19215.0</td><td>26005.866</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231025&quot;</td><td>13.65</td><td>13.77</td><td>13.58</td><td>13.62</td><td>13.67</td><td>-0.05</td><td>-0.3658</td><td>18484.0</td><td>25274.163</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220715&quot;</td><td>13.61</td><td>13.66</td><td>13.12</td><td>13.13</td><td>13.59</td><td>-0.46</td><td>-3.3848</td><td>32967.65</td><td>44114.064</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220714&quot;</td><td>13.54</td><td>13.75</td><td>13.5</td><td>13.59</td><td>13.54</td><td>0.05</td><td>0.3693</td><td>21967.0</td><td>29851.164</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220713&quot;</td><td>13.55</td><td>13.63</td><td>13.39</td><td>13.54</td><td>13.61</td><td>-0.07</td><td>-0.5143</td><td>22793.0</td><td>30714.624</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220712&quot;</td><td>13.65</td><td>13.69</td><td>13.41</td><td>13.61</td><td>13.65</td><td>-0.04</td><td>-0.293</td><td>29679.0</td><td>40146.31</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220711&quot;</td><td>13.2</td><td>13.96</td><td>13.07</td><td>13.65</td><td>13.18</td><td>0.47</td><td>3.566</td><td>62827.0</td><td>85869.11</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 11)\n",
       "┌───────────┬────────────┬───────┬───────┬───┬────────┬─────────┬───────────┬────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ open  ┆ high  ┆ … ┆ change ┆ pct_chg ┆ vol       ┆ amount     │\n",
       "│ ---       ┆ ---        ┆ ---   ┆ ---   ┆   ┆ ---    ┆ ---     ┆ ---       ┆ ---        │\n",
       "│ str       ┆ str        ┆ f64   ┆ f64   ┆   ┆ f64    ┆ f64     ┆ f64       ┆ f64        │\n",
       "╞═══════════╪════════════╪═══════╪═══════╪═══╪════════╪═════════╪═══════════╪════════════╡\n",
       "│ 002462.SZ ┆ 20231031   ┆ 14.75 ┆ 14.9  ┆ … ┆ -0.05  ┆ -0.339  ┆ 65859.96  ┆ 96984.271  │\n",
       "│ 002462.SZ ┆ 20231030   ┆ 13.88 ┆ 14.89 ┆ … ┆ 0.82   ┆ 5.8866  ┆ 123932.16 ┆ 180119.372 │\n",
       "│ 002462.SZ ┆ 20231027   ┆ 13.7  ┆ 13.98 ┆ … ┆ 0.29   ┆ 2.1261  ┆ 35782.0   ┆ 49386.168  │\n",
       "│ 002462.SZ ┆ 20231026   ┆ 13.49 ┆ 13.68 ┆ … ┆ 0.02   ┆ 0.1468  ┆ 19215.0   ┆ 26005.866  │\n",
       "│ 002462.SZ ┆ 20231025   ┆ 13.65 ┆ 13.77 ┆ … ┆ -0.05  ┆ -0.3658 ┆ 18484.0   ┆ 25274.163  │\n",
       "│ …         ┆ …          ┆ …     ┆ …     ┆ … ┆ …      ┆ …       ┆ …         ┆ …          │\n",
       "│ 002462.SZ ┆ 20220715   ┆ 13.61 ┆ 13.66 ┆ … ┆ -0.46  ┆ -3.3848 ┆ 32967.65  ┆ 44114.064  │\n",
       "│ 002462.SZ ┆ 20220714   ┆ 13.54 ┆ 13.75 ┆ … ┆ 0.05   ┆ 0.3693  ┆ 21967.0   ┆ 29851.164  │\n",
       "│ 002462.SZ ┆ 20220713   ┆ 13.55 ┆ 13.63 ┆ … ┆ -0.07  ┆ -0.5143 ┆ 22793.0   ┆ 30714.624  │\n",
       "│ 002462.SZ ┆ 20220712   ┆ 13.65 ┆ 13.69 ┆ … ┆ -0.04  ┆ -0.293  ┆ 29679.0   ┆ 40146.31   │\n",
       "│ 002462.SZ ┆ 20220711   ┆ 13.2  ┆ 13.96 ┆ … ┆ 0.47   ┆ 3.566   ┆ 62827.0   ┆ 85869.11   │\n",
       "└───────────┴────────────┴───────┴───────┴───┴────────┴─────────┴───────────┴────────────┘"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq = pro.daily(\n",
    "    ts_code=\"002462.sz\",\n",
    "    start_date=format(start_date, \"%Y%m%d\"),\n",
    "    end_date=format(end_date, \"%Y%m%d\"),\n",
    ")\n",
    "hq = pl.from_pandas(hq)\n",
    "hq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "ffb395d3-a662-41cc-8a2b-81d111572a7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_codes = (\n",
    "    d1.select(\n",
    "        证券代码=(\n",
    "            pl.when(pl.col(\"证券代码\").str.head(1).is_in([\"0\", \"3\"]))\n",
    "            .then(pl.format(\"{}.SZ\", pl.col(\"证券代码\")))\n",
    "            .when(pl.col(\"证券代码\").str.head(1) == \"6\")\n",
    "            .then(pl.format(\"{}.SH\", pl.col(\"证券代码\")))\n",
    "        )\n",
    "    )\n",
    "    .to_series()\n",
    "    .unique()\n",
    "    .sort()\n",
    "    .to_list()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "0b7a4869-b4c4-4821-aaf8-57125a8c6ab0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['000096.SZ',\n",
       " '000532.SZ',\n",
       " '000559.SZ',\n",
       " '000599.SZ',\n",
       " '000655.SZ',\n",
       " '000698.SZ',\n",
       " '000712.SZ',\n",
       " '000717.SZ',\n",
       " '000751.SZ',\n",
       " '000823.SZ',\n",
       " '000900.SZ',\n",
       " '000903.SZ',\n",
       " '002023.SZ',\n",
       " '002054.SZ',\n",
       " '002055.SZ',\n",
       " '002106.SZ',\n",
       " '002111.SZ',\n",
       " '002136.SZ',\n",
       " '002224.SZ',\n",
       " '002225.SZ',\n",
       " '002228.SZ',\n",
       " '002288.SZ',\n",
       " '002331.SZ',\n",
       " '002381.SZ',\n",
       " '002382.SZ',\n",
       " '002392.SZ',\n",
       " '002440.SZ',\n",
       " '002442.SZ',\n",
       " '002462.SZ',\n",
       " '002479.SZ',\n",
       " '002492.SZ',\n",
       " '002521.SZ',\n",
       " '002616.SZ',\n",
       " '002637.SZ',\n",
       " '002661.SZ',\n",
       " '002672.SZ',\n",
       " '002688.SZ',\n",
       " '002702.SZ',\n",
       " '002734.SZ',\n",
       " '002743.SZ',\n",
       " '002753.SZ',\n",
       " '002842.SZ',\n",
       " '002946.SZ',\n",
       " '002956.SZ',\n",
       " '002996.SZ',\n",
       " '002998.SZ',\n",
       " '300006.SZ',\n",
       " '300016.SZ',\n",
       " '300022.SZ',\n",
       " '300107.SZ',\n",
       " '300132.SZ',\n",
       " '300155.SZ',\n",
       " '300190.SZ',\n",
       " '300214.SZ',\n",
       " '300215.SZ',\n",
       " '300221.SZ',\n",
       " '300241.SZ',\n",
       " '300242.SZ',\n",
       " '300259.SZ',\n",
       " '300279.SZ',\n",
       " '300281.SZ',\n",
       " '300283.SZ',\n",
       " '300304.SZ',\n",
       " '300368.SZ',\n",
       " '300385.SZ',\n",
       " '300422.SZ',\n",
       " '300429.SZ',\n",
       " '300464.SZ',\n",
       " '300473.SZ',\n",
       " '300538.SZ',\n",
       " '300543.SZ',\n",
       " '300641.SZ',\n",
       " '300642.SZ',\n",
       " '300651.SZ',\n",
       " '300701.SZ',\n",
       " '300708.SZ',\n",
       " '300905.SZ',\n",
       " '600015.SH',\n",
       " '600022.SH',\n",
       " '600051.SH',\n",
       " '600097.SH',\n",
       " '600120.SH',\n",
       " '600178.SH',\n",
       " '600231.SH',\n",
       " '600261.SH',\n",
       " '600269.SH',\n",
       " '600273.SH',\n",
       " '600287.SH',\n",
       " '600292.SH',\n",
       " '600300.SH',\n",
       " '600336.SH',\n",
       " '600408.SH',\n",
       " '600429.SH',\n",
       " '600513.SH',\n",
       " '600525.SH',\n",
       " '600590.SH',\n",
       " '600597.SH',\n",
       " '600626.SH',\n",
       " '600638.SH',\n",
       " '600643.SH',\n",
       " '600648.SH',\n",
       " '600727.SH',\n",
       " '600729.SH',\n",
       " '600735.SH',\n",
       " '600793.SH',\n",
       " '600800.SH',\n",
       " '600810.SH',\n",
       " '600827.SH',\n",
       " '600829.SH',\n",
       " '600835.SH',\n",
       " '600839.SH',\n",
       " '600843.SH',\n",
       " '600844.SH',\n",
       " '600894.SH',\n",
       " '600960.SH',\n",
       " '600967.SH',\n",
       " '600980.SH',\n",
       " '601002.SH',\n",
       " '601077.SH',\n",
       " '601166.SH',\n",
       " '601992.SH',\n",
       " '603002.SH',\n",
       " '603111.SH',\n",
       " '603113.SH',\n",
       " '603186.SH',\n",
       " '603214.SH',\n",
       " '603385.SH',\n",
       " '603386.SH',\n",
       " '603577.SH',\n",
       " '603626.SH',\n",
       " '603686.SH',\n",
       " '603739.SH',\n",
       " '603878.SH',\n",
       " '603897.SH',\n",
       " '603967.SH',\n",
       " '603988.SH',\n",
       " '605066.SH',\n",
       " '605158.SH',\n",
       " '605196.SH',\n",
       " '605258.SH',\n",
       " '605288.SH',\n",
       " '688011.SH',\n",
       " '688069.SH',\n",
       " '688165.SH',\n",
       " '688299.SH',\n",
       " '688321.SH',\n",
       " '688360.SH',\n",
       " '688393.SH',\n",
       " '688660.SH']"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "804203ab-5387-48a9-a11d-75f2cd899d27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "149"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ts_codes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "4111fa04-9616-4796-bd4f-3b9f6e36db47",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "a7afe109-a2c1-479d-bbd6-6f362b0e4718",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "23659da10e304b1a9094264ef91906dc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/149 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hq = []\n",
    "for ts_code in tqdm(ts_codes):\n",
    "    df = pro.daily(\n",
    "        ts_code=ts_code,\n",
    "        start_date=format(start_date, \"%Y%m%d\"),\n",
    "        end_date=format(end_date, \"%Y%m%d\"),\n",
    "    )\n",
    "    if df is not None and not df.empty:\n",
    "        polars_df = pl.from_pandas(df)\n",
    "        hq.append(polars_df)\n",
    "\n",
    "# 后续如果需要合并所有获取到的数据\n",
    "if hq:\n",
    "    hq = pl.concat(hq)\n",
    "else:\n",
    "    hq = pl.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "1296e0d9-a189-41c9-b6d0-4371e539e5f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq.write_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "6a92e0c0-f936-4235-96fd-c0036d04c178",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.read_parquet(\"daily.parquet\")\n",
    "\n",
    "hq = hq.with_columns(\n",
    "    pl.col(\"ts_code\").str.head(6),\n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "4ea01171-dfe2-457d-8d57-9d9ca3ec436b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 28)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花费</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th><th>vratio</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>237</td><td>&quot;湘财&quot;</td><td>2023-05-09</td><td>&quot;09:33:16&quot;</td><td>&quot;601166&quot;</td><td>&quot;兴业银行&quot;</td><td>&quot;卖出&quot;</td><td>17.75</td><td>3000.0</td><td>53250.0</td><td>8.52</td><td>53.25</td><td>0.53</td><td>0.0</td><td>53187.7</td><td>0.00016</td><td>0.001</td><td>0.00001</td><td>17.77</td><td>17.94</td><td>17.5</td><td>17.56</td><td>17.63</td><td>-0.07</td><td>-0.3971</td><td>1.6276e6</td><td>2.8810e6</td><td>0.000018</td></tr><tr><td>48</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:30:19&quot;</td><td>&quot;300368&quot;</td><td>&quot;汇金股份&quot;</td><td>&quot;卖出&quot;</td><td>6.2</td><td>100.0</td><td>620.0</td><td>0.1</td><td>0.62</td><td>0.01</td><td>0.0</td><td>619.28</td><td>0.000161</td><td>0.001</td><td>0.000016</td><td>6.18</td><td>6.25</td><td>5.95</td><td>6.06</td><td>6.2</td><td>-0.14</td><td>-2.2581</td><td>49219.99</td><td>29911.915</td><td>0.00002</td></tr><tr><td>49</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:30:39&quot;</td><td>&quot;002996&quot;</td><td>&quot;顺博合金&quot;</td><td>&quot;卖出&quot;</td><td>13.64</td><td>100.0</td><td>1364.0</td><td>0.22</td><td>1.36</td><td>0.01</td><td>0.0</td><td>1362.42</td><td>0.000161</td><td>0.000997</td><td>0.000007</td><td>13.57</td><td>13.93</td><td>13.26</td><td>13.82</td><td>13.64</td><td>0.18</td><td>1.3196</td><td>38258.22</td><td>52122.119</td><td>0.000026</td></tr><tr><td>235</td><td>&quot;湘财&quot;</td><td>2023-04-25</td><td>&quot;13:35:07&quot;</td><td>&quot;601166&quot;</td><td>&quot;兴业银行&quot;</td><td>&quot;买入&quot;</td><td>17.15</td><td>3000.0</td><td>51450.0</td><td>8.23</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-51458.74</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>16.96</td><td>17.29</td><td>16.96</td><td>17.27</td><td>16.93</td><td>0.34</td><td>2.0083</td><td>995234.22</td><td>1.7071e6</td><td>0.00003</td></tr><tr><td>46</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:25:00&quot;</td><td>&quot;002998&quot;</td><td>&quot;优彩资源&quot;</td><td>&quot;卖出&quot;</td><td>6.73</td><td>100.0</td><td>673.0</td><td>0.11</td><td>0.67</td><td>0.01</td><td>0.0</td><td>672.22</td><td>0.000163</td><td>0.000996</td><td>0.000015</td><td>6.73</td><td>6.73</td><td>6.56</td><td>6.64</td><td>6.72</td><td>-0.08</td><td>-1.1905</td><td>16145.0</td><td>10687.614</td><td>0.000062</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>30</td><td>&quot;湘财&quot;</td><td>2022-10-11</td><td>&quot;09:41:06&quot;</td><td>&quot;600231&quot;</td><td>&quot;凌钢股份&quot;</td><td>&quot;买入&quot;</td><td>2.2</td><td>62700.0</td><td>137940.0</td><td>22.07</td><td>0.0</td><td>1.29</td><td>0.0</td><td>-137963.36</td><td>0.00016</td><td>0.0</td><td>0.000009</td><td>2.21</td><td>2.22</td><td>2.15</td><td>2.2</td><td>2.21</td><td>-0.01</td><td>-0.4525</td><td>61371.2</td><td>13386.328</td><td>0.010217</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.57</td><td>6.57</td><td>6.49</td><td>6.51</td><td>6.57</td><td>-0.06</td><td>-0.9132</td><td>20737.5</td><td>13537.134</td><td>0.010319</td></tr><tr><td>142</td><td>&quot;湘财&quot;</td><td>2023-02-21</td><td>&quot;09:37:16&quot;</td><td>&quot;300385&quot;</td><td>&quot;雪浪环境&quot;</td><td>&quot;买入&quot;</td><td>6.5293</td><td>11500.0</td><td>75087.0</td><td>12.01</td><td>0.0</td><td>0.77</td><td>0.0</td><td>-75099.01</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.53</td><td>6.59</td><td>6.45</td><td>6.52</td><td>6.5</td><td>0.02</td><td>0.3077</td><td>11136.0</td><td>7250.686</td><td>0.010327</td></tr><tr><td>152</td><td>&quot;湘财&quot;</td><td>2023-03-01</td><td>&quot;09:31:46&quot;</td><td>&quot;688069&quot;</td><td>&quot;德林海环保&quot;</td><td>&quot;卖出&quot;</td><td>28.2838</td><td>2600.0</td><td>73538.0</td><td>11.77</td><td>73.58</td><td>0.77</td><td>0.0</td><td>73451.88</td><td>0.00016</td><td>0.001001</td><td>0.00001</td><td>28.52</td><td>28.86</td><td>28.27</td><td>28.67</td><td>28.54</td><td>0.13</td><td>0.4555</td><td>2416.06</td><td>6897.976</td><td>0.010761</td></tr><tr><td>27</td><td>&quot;湘财&quot;</td><td>2022-10-11</td><td>&quot;09:37:32&quot;</td><td>&quot;600261&quot;</td><td>&quot;阳光照明&quot;</td><td>&quot;买入&quot;</td><td>3.1134</td><td>44600.0</td><td>138857.0</td><td>22.22</td><td>0.0</td><td>1.33</td><td>0.0</td><td>-138880.55</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>3.14</td><td>3.15</td><td>3.06</td><td>3.13</td><td>3.11</td><td>0.02</td><td>0.6431</td><td>41397.03</td><td>12872.499</td><td>0.010774</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 28)\n",
       "┌──────┬──────┬────────────┬──────────┬───┬─────────┬───────────┬───────────┬──────────┐\n",
       "│ 序号 ┆ 券商 ┆ 交易日期   ┆ 交易时间 ┆ … ┆ pct_chg ┆ vol       ┆ amount    ┆ vratio   │\n",
       "│ ---  ┆ ---  ┆ ---        ┆ ---      ┆   ┆ ---     ┆ ---       ┆ ---       ┆ ---      │\n",
       "│ u32  ┆ str  ┆ date       ┆ str      ┆   ┆ f64     ┆ f64       ┆ f64       ┆ f64      │\n",
       "╞══════╪══════╪════════════╪══════════╪═══╪═════════╪═══════════╪═══════════╪══════════╡\n",
       "│ 237  ┆ 湘财 ┆ 2023-05-09 ┆ 09:33:16 ┆ … ┆ -0.3971 ┆ 1.6276e6  ┆ 2.8810e6  ┆ 0.000018 │\n",
       "│ 48   ┆ 湘财 ┆ 2022-10-25 ┆ 09:30:19 ┆ … ┆ -2.2581 ┆ 49219.99  ┆ 29911.915 ┆ 0.00002  │\n",
       "│ 49   ┆ 湘财 ┆ 2022-10-25 ┆ 09:30:39 ┆ … ┆ 1.3196  ┆ 38258.22  ┆ 52122.119 ┆ 0.000026 │\n",
       "│ 235  ┆ 湘财 ┆ 2023-04-25 ┆ 13:35:07 ┆ … ┆ 2.0083  ┆ 995234.22 ┆ 1.7071e6  ┆ 0.00003  │\n",
       "│ 46   ┆ 湘财 ┆ 2022-10-25 ┆ 09:25:00 ┆ … ┆ -1.1905 ┆ 16145.0   ┆ 10687.614 ┆ 0.000062 │\n",
       "│ …    ┆ …    ┆ …          ┆ …        ┆ … ┆ …       ┆ …         ┆ …         ┆ …        │\n",
       "│ 30   ┆ 湘财 ┆ 2022-10-11 ┆ 09:41:06 ┆ … ┆ -0.4525 ┆ 61371.2   ┆ 13386.328 ┆ 0.010217 │\n",
       "│ 3    ┆ 湘财 ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -0.9132 ┆ 20737.5   ┆ 13537.134 ┆ 0.010319 │\n",
       "│ 142  ┆ 湘财 ┆ 2023-02-21 ┆ 09:37:16 ┆ … ┆ 0.3077  ┆ 11136.0   ┆ 7250.686  ┆ 0.010327 │\n",
       "│ 152  ┆ 湘财 ┆ 2023-03-01 ┆ 09:31:46 ┆ … ┆ 0.4555  ┆ 2416.06   ┆ 6897.976  ┆ 0.010761 │\n",
       "│ 27   ┆ 湘财 ┆ 2022-10-11 ┆ 09:37:32 ┆ … ┆ 0.6431  ┆ 41397.03  ┆ 12872.499 ┆ 0.010774 │\n",
       "└──────┴──────┴────────────┴──────────┴───┴─────────┴───────────┴───────────┴──────────┘"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1.join(\n",
    "    hq, left_on=[\"交易日期\", \"证券代码\"], right_on=[\"trade_date\", \"ts_code\"], how=\"left\"\n",
    ").with_columns(\n",
    "    vratio=pl.col(\"成交数量\") / 100 / pl.col(\"vol\"),\n",
    ").sort(\"vratio\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "39d1236e-9633-419a-aee2-e49464d2897f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (72_671, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花费</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000096&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000532&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000559&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000599&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000655&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;688299&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688321&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688360&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688393&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (72_671, 19)\n",
       "┌────────────┬──────────┬──────┬──────┬───┬──────────┬──────────┬──────────┬──────────┐\n",
       "│ 日期       ┆ 证券代码 ┆ 序号 ┆ 券商 ┆ … ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 ┆ 结余数量 │\n",
       "│ ---        ┆ ---      ┆ ---  ┆ ---  ┆   ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ date       ┆ str      ┆ u32  ┆ str  ┆   ┆ f64      ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞════════════╪══════════╪══════╪══════╪═══╪══════════╪══════════╪══════════╪══════════╡\n",
       "│ 2022-07-11 ┆ 000096   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000532   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000559   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000599   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000655   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ …          ┆ …        ┆ …    ┆ …    ┆ … ┆ …        ┆ …        ┆ …        ┆ …        │\n",
       "│ 2023-10-31 ┆ 688299   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688321   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688360   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688393   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688660   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 20200.0  │\n",
       "└────────────┴──────────┴──────┴──────┴───┴──────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d4 = (\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    .sort([\"日期\", \"证券代码\"])\n",
    "    .with_columns(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .otherwise(0)\n",
    "            .cum_sum()\n",
    "            .over(\"证券代码\")\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "d4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "417a63d3-0982-4988-93df-ecae2d9c5254",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (478, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>持股市值</th><th>发生金额</th><th>转账金额</th><th>现金余额</th><th>总资产</th></tr><tr><td>date</td><td>f64</td><td>f64</td><td>i32</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>703040.0</td><td>-699551.12</td><td>1000000</td><td>300448.88</td><td>1.0035e6</td></tr><tr><td>2022-07-12</td><td>707714.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0082e6</td></tr><tr><td>2022-07-13</td><td>713855.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0143e6</td></tr><tr><td>2022-07-14</td><td>710079.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0105e6</td></tr><tr><td>2022-07-15</td><td>692377.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>992825.88</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-27</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td></tr><tr><td>2023-10-28</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td></tr><tr><td>2023-10-29</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td></tr><tr><td>2023-10-30</td><td>686345.0</td><td>-94884.93</td><td>0</td><td>415961.03</td><td>1.1023e6</td></tr><tr><td>2023-10-31</td><td>748947.0</td><td>6490.78</td><td>0</td><td>422451.81</td><td>1.1714e6</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (478, 6)\n",
       "┌────────────┬──────────┬────────────┬──────────┬───────────┬───────────┐\n",
       "│ 日期       ┆ 持股市值 ┆ 发生金额   ┆ 转账金额 ┆ 现金余额  ┆ 总资产    │\n",
       "│ ---        ┆ ---      ┆ ---        ┆ ---      ┆ ---       ┆ ---       │\n",
       "│ date       ┆ f64      ┆ f64        ┆ i32      ┆ f64       ┆ f64       │\n",
       "╞════════════╪══════════╪════════════╪══════════╪═══════════╪═══════════╡\n",
       "│ 2022-07-11 ┆ 703040.0 ┆ -699551.12 ┆ 1000000  ┆ 300448.88 ┆ 1.0035e6  │\n",
       "│ 2022-07-12 ┆ 707714.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 1.0082e6  │\n",
       "│ 2022-07-13 ┆ 713855.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 1.0143e6  │\n",
       "│ 2022-07-14 ┆ 710079.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 1.0105e6  │\n",
       "│ 2022-07-15 ┆ 692377.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 992825.88 │\n",
       "│ …          ┆ …        ┆ …          ┆ …        ┆ …         ┆ …         │\n",
       "│ 2023-10-27 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ 510845.96 ┆ 1.0820e6  │\n",
       "│ 2023-10-28 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ 510845.96 ┆ 1.0820e6  │\n",
       "│ 2023-10-29 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ 510845.96 ┆ 1.0820e6  │\n",
       "│ 2023-10-30 ┆ 686345.0 ┆ -94884.93  ┆ 0        ┆ 415961.03 ┆ 1.1023e6  │\n",
       "│ 2023-10-31 ┆ 748947.0 ┆ 6490.78    ┆ 0        ┆ 422451.81 ┆ 1.1714e6  │\n",
       "└────────────┴──────────┴────────────┴──────────┴───────────┴───────────┘"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d5 = (\n",
    "    d4.join(\n",
    "        hq, left_on=[\"日期\", \"证券代码\"], right_on=[\"trade_date\", \"ts_code\"], how=\"left\"\n",
    "    )\n",
    "    .sort([\"证券代码\", \"日期\"])\n",
    "    .with_columns(close=pl.col(\"close\").fill_null(strategy=\"forward\").over(\"证券代码\"))\n",
    "    .with_columns(持股市值=pl.col(\"结余数量\") * pl.col(\"close\"))\n",
    "    .group_by(\"日期\")\n",
    "    .agg(pl.col(\"持股市值\").sum(), pl.col(\"发生金额\").sum())\n",
    "    .sort(\"日期\")\n",
    "    .with_columns(\n",
    "        转账金额=pl.when(pl.int_range(0, pl.len()) == 0).then(100_0000).otherwise(0)\n",
    "    )\n",
    "    .with_columns(\n",
    "        现金余额=(pl.col(\"转账金额\") + pl.col(\"发生金额\")).cum_sum(),\n",
    "    )\n",
    "    .with_columns(总资产=(pl.col(\"持股市值\") + pl.col(\"现金余额\")))\n",
    ")\n",
    "d5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "e424e39b-2569-4041-bc4d-16faabb82172",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c64c4830f4ba419ebf071575f7ca0d04",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '持股市值', '发生金额', '转账金额', '现金余额', '总资产'], table_name='0.…"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "dd539fda-6cf8-4985-a2b2-133f2520caf2",
   "metadata": {},
   "outputs": [],
   "source": [
    "ihq = pro.index_daily(\n",
    "    ts_code=\"000300.SH\",\n",
    "    start_date=format(start_date, \"%Y%m%d\"),\n",
    "    end_date=format(end_date, \"%Y%m%d\"),\n",
    "    fields=\"ts_code,trade_date,pct_chg\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "5c0ff408-ef39-48cd-86b9-a229b9382f58",
   "metadata": {},
   "outputs": [],
   "source": [
    "pl.from_pandas(ihq).write_parquet(\"index_daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "9a467f51-bca1-44bb-b054-e21ffbf55e1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>pct_chg</th><th>car</th><th>沪深300</th></tr><tr><td>str</td><td>date</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;000300.SH&quot;</td><td>2022-07-11</td><td>0.983254</td><td>0.983254</td><td>98325.4</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-12</td><td>0.990585</td><td>0.973997</td><td>97399.666359</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-13</td><td>1.001818</td><td>0.975767</td><td>97576.738952</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-14</td><td>1.000142</td><td>0.975906</td><td>97590.594849</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-15</td><td>0.982983</td><td>0.959299</td><td>95929.895697</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-25</td><td>1.004969</td><td>0.791288</td><td>79128.845378</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-26</td><td>1.002764</td><td>0.793476</td><td>79347.557506</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-27</td><td>1.013727</td><td>0.804368</td><td>80436.761428</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-30</td><td>1.006003</td><td>0.809196</td><td>80919.623307</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-31</td><td>0.996856</td><td>0.806652</td><td>80665.212012</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 5)\n",
       "┌───────────┬────────────┬──────────┬──────────┬──────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ pct_chg  ┆ car      ┆ 沪深300      │\n",
       "│ ---       ┆ ---        ┆ ---      ┆ ---      ┆ ---          │\n",
       "│ str       ┆ date       ┆ f64      ┆ f64      ┆ f64          │\n",
       "╞═══════════╪════════════╪══════════╪══════════╪══════════════╡\n",
       "│ 000300.SH ┆ 2022-07-11 ┆ 0.983254 ┆ 0.983254 ┆ 98325.4      │\n",
       "│ 000300.SH ┆ 2022-07-12 ┆ 0.990585 ┆ 0.973997 ┆ 97399.666359 │\n",
       "│ 000300.SH ┆ 2022-07-13 ┆ 1.001818 ┆ 0.975767 ┆ 97576.738952 │\n",
       "│ 000300.SH ┆ 2022-07-14 ┆ 1.000142 ┆ 0.975906 ┆ 97590.594849 │\n",
       "│ 000300.SH ┆ 2022-07-15 ┆ 0.982983 ┆ 0.959299 ┆ 95929.895697 │\n",
       "│ …         ┆ …          ┆ …        ┆ …        ┆ …            │\n",
       "│ 000300.SH ┆ 2023-10-25 ┆ 1.004969 ┆ 0.791288 ┆ 79128.845378 │\n",
       "│ 000300.SH ┆ 2023-10-26 ┆ 1.002764 ┆ 0.793476 ┆ 79347.557506 │\n",
       "│ 000300.SH ┆ 2023-10-27 ┆ 1.013727 ┆ 0.804368 ┆ 80436.761428 │\n",
       "│ 000300.SH ┆ 2023-10-30 ┆ 1.006003 ┆ 0.809196 ┆ 80919.623307 │\n",
       "│ 000300.SH ┆ 2023-10-31 ┆ 0.996856 ┆ 0.806652 ┆ 80665.212012 │\n",
       "└───────────┴────────────┴──────────┴──────────┴──────────────┘"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ihq = pl.read_parquet(\"index_daily.parquet\")\n",
    "ihq = (\n",
    "    ihq.with_columns(\n",
    "        pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"), pl.col(\"pct_chg\") / 100 + 1\n",
    "    )\n",
    "    .sort(\"trade_date\")\n",
    "    .with_columns(car=pl.col(\"pct_chg\").cum_prod())\n",
    "    .with_columns(沪深300=pl.col(\"car\") * 100_000)\n",
    ")\n",
    "ihq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "8b619c83-dab7-4cd9-93ba-4465b4bddfb2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0aef71e814c441aea29e789bd88a8945",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['ts_code', 'trade_date', 'pct_chg', 'car', '沪深300'], table_n…"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(ihq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "c3a14b27-3d7b-4139-a73c-6be68b011d2a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 10)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>持股市值</th><th>发生金额</th><th>转账金额</th><th>现金余额</th><th>总资产</th><th>ts_code</th><th>pct_chg</th><th>car</th><th>沪深300</th></tr><tr><td>date</td><td>f64</td><td>f64</td><td>i32</td><td>f64</td><td>f64</td><td>str</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>703040.0</td><td>-699551.12</td><td>1000000</td><td>300448.88</td><td>1.0035e6</td><td>&quot;000300.SH&quot;</td><td>0.983254</td><td>0.983254</td><td>98325.4</td></tr><tr><td>2022-07-12</td><td>707714.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0082e6</td><td>&quot;000300.SH&quot;</td><td>0.990585</td><td>0.973997</td><td>97399.666359</td></tr><tr><td>2022-07-13</td><td>713855.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0143e6</td><td>&quot;000300.SH&quot;</td><td>1.001818</td><td>0.975767</td><td>97576.738952</td></tr><tr><td>2022-07-14</td><td>710079.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0105e6</td><td>&quot;000300.SH&quot;</td><td>1.000142</td><td>0.975906</td><td>97590.594849</td></tr><tr><td>2022-07-15</td><td>692377.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>992825.88</td><td>&quot;000300.SH&quot;</td><td>0.982983</td><td>0.959299</td><td>95929.895697</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-25</td><td>556522.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0674e6</td><td>&quot;000300.SH&quot;</td><td>1.004969</td><td>0.791288</td><td>79128.845378</td></tr><tr><td>2023-10-26</td><td>557109.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0680e6</td><td>&quot;000300.SH&quot;</td><td>1.002764</td><td>0.793476</td><td>79347.557506</td></tr><tr><td>2023-10-27</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td><td>&quot;000300.SH&quot;</td><td>1.013727</td><td>0.804368</td><td>80436.761428</td></tr><tr><td>2023-10-30</td><td>686345.0</td><td>-94884.93</td><td>0</td><td>415961.03</td><td>1.1023e6</td><td>&quot;000300.SH&quot;</td><td>1.006003</td><td>0.809196</td><td>80919.623307</td></tr><tr><td>2023-10-31</td><td>748947.0</td><td>6490.78</td><td>0</td><td>422451.81</td><td>1.1714e6</td><td>&quot;000300.SH&quot;</td><td>0.996856</td><td>0.806652</td><td>80665.212012</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 10)\n",
       "┌────────────┬──────────┬────────────┬──────────┬───┬───────────┬──────────┬──────────┬──────────────┐\n",
       "│ 日期       ┆ 持股市值 ┆ 发生金额   ┆ 转账金额 ┆ … ┆ ts_code   ┆ pct_chg  ┆ car      ┆ 沪深300      │\n",
       "│ ---        ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---       ┆ ---      ┆ ---      ┆ ---          │\n",
       "│ date       ┆ f64      ┆ f64        ┆ i32      ┆   ┆ str       ┆ f64      ┆ f64      ┆ f64          │\n",
       "╞════════════╪══════════╪════════════╪══════════╪═══╪═══════════╪══════════╪══════════╪══════════════╡\n",
       "│ 2022-07-11 ┆ 703040.0 ┆ -699551.12 ┆ 1000000  ┆ … ┆ 000300.SH ┆ 0.983254 ┆ 0.983254 ┆ 98325.4      │\n",
       "│ 2022-07-12 ┆ 707714.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 0.990585 ┆ 0.973997 ┆ 97399.666359 │\n",
       "│ 2022-07-13 ┆ 713855.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 1.001818 ┆ 0.975767 ┆ 97576.738952 │\n",
       "│ 2022-07-14 ┆ 710079.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 1.000142 ┆ 0.975906 ┆ 97590.594849 │\n",
       "│ 2022-07-15 ┆ 692377.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 0.982983 ┆ 0.959299 ┆ 95929.895697 │\n",
       "│ …          ┆ …        ┆ …          ┆ …        ┆ … ┆ …         ┆ …        ┆ …        ┆ …            │\n",
       "│ 2023-10-25 ┆ 556522.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 1.004969 ┆ 0.791288 ┆ 79128.845378 │\n",
       "│ 2023-10-26 ┆ 557109.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 1.002764 ┆ 0.793476 ┆ 79347.557506 │\n",
       "│ 2023-10-27 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ … ┆ 000300.SH ┆ 1.013727 ┆ 0.804368 ┆ 80436.761428 │\n",
       "│ 2023-10-30 ┆ 686345.0 ┆ -94884.93  ┆ 0        ┆ … ┆ 000300.SH ┆ 1.006003 ┆ 0.809196 ┆ 80919.623307 │\n",
       "│ 2023-10-31 ┆ 748947.0 ┆ 6490.78    ┆ 0        ┆ … ┆ 000300.SH ┆ 0.996856 ┆ 0.806652 ┆ 80665.212012 │\n",
       "└────────────┴──────────┴────────────┴──────────┴───┴───────────┴──────────┴──────────┴──────────────┘"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d6 = d5.join(ihq, left_on=\"日期\", right_on=\"trade_date\")\n",
    "d6 = d6.unpivot(\n",
    "    on=[\"总资产\", \"沪深300\"], index=\"日期\", variable_name=\"资产类型\", value_name=\"财富\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "22420a7e-13fb-4f62-a37c-0f7798409d53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "55a0897c16774a7aa9e49de5583b132b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '资产类型', '财富'], table_name='0.9071355458011491', theme=…"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5954774c-3ee7-42d4-81b3-befdd764552b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
